﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using JYH.Data;
using Newtonsoft.Json;
using JYH.Model;
using JYH.DataAccess;
using JYH.Framework;
using JYH.Model.API;


namespace JYH.Business
{
    /// <summary>
    ///  WmsTradeOrderInfo
    /// </summary>
    public class WmsTradeOrderInfoBLL : BusinessBase<WmsTradeOrderInfo>
    {
        /// <summary>
        ///  WmsTradeOrderInfo
        /// </summary>
        public WmsTradeOrderInfoBLL()
        {
            myDataBase = DB.D();

            this.SqlSelect = @"; Select WmsTradeOrderInfo.* From WmsTradeOrderInfo ";
        }

        //调拨单
        public string DbSqlSelect = @"; Select WmsTradeOrderInfo.*,FromInfo.WarehouseName WarehouseName,ToInfo.WarehouseName ToWarehouseName From WmsTradeOrderInfo
                                          left JOIN BasWarehouseInfo FromInfo on FromInfo.WarehouseID=WmsTradeOrderInfo.FromWarehouseID
                                          left JOIN BasWarehouseInfo ToInfo on ToInfo.WarehouseID=WmsTradeOrderInfo.ToWarehouseID ";

        //备料单
        public string MPSqlSelect = @"; Select WmsTradeOrderInfo.*,FromInfo.WarehouseName WarehouseName From WmsTradeOrderInfo
left JOIN BasWarehouseInfo FromInfo on FromInfo.WarehouseID=WmsTradeOrderInfo.FromWarehouseID ";

        //生产领料单
        public string MFGSqlSelect = @"; Select WmsTradeOrderInfo.*,FromInfo.WarehouseName WarehouseName From WmsTradeOrderInfo
left JOIN BasWarehouseInfo FromInfo on FromInfo.WarehouseID=WmsTradeOrderInfo.FromWarehouseID ";

        /// <summary>
        /// 保存单据主档 返回单号
        /// </summary>
        /// <param name="info"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public bool SaveWmsOrderInfo(WmsTradeOrderInfo info, ref ResponseJson<WmsTradeOrderInfo> response)
        {
            Database db = DB.D();
            db.BeginTransaction();
            StringBuilder sb = new StringBuilder();
            Sql sql = new Sql();
            string Msg = string.Empty;
            try
            {
                WmsTradeOrderInfo tradeOrderInfo = new WmsTradeOrderInfo();
                if (!string.IsNullOrWhiteSpace(info.TradeOrderID))
                {
                    sql.Append($"Where WmsTradeOrderInfo.TradeOrderID='{info.TradeOrderID}'");
                    tradeOrderInfo = this.GetSingeModel(sql);
                    if (tradeOrderInfo.TradeOrderID == info.TradeOrderID)
                    {
                        //有数据
                        tradeOrderInfo.TradeOrderNO = info.TradeOrderNO;
                        tradeOrderInfo.FromWarehouseID = info.FromWarehouseID;
                        tradeOrderInfo.ToWarehouseID = info.ToWarehouseID;
                        tradeOrderInfo.SourceOrderNO = info.SourceOrderNO;
                        tradeOrderInfo.ProductNO = info.ProductNO;
                        tradeOrderInfo.Remark = info.Remark;
                        tradeOrderInfo.TradeQty = info.TradeQty;
                        tradeOrderInfo.TradeDate = DateTime.Now.ToString("yyyy-MM-dd");
                        tradeOrderInfo.UpdateUser = SysUserInfoBLL.GetCookieUser().UserName;
                        tradeOrderInfo.UpdateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                        sb.Append(BusinessTool.GetUpdateSql(tradeOrderInfo));
                    }
                    else
                    {
                        response.Message = string.Format(SysMessageInfoBLL.GetMessageInfo("WarehouseInfo01"), info.TradeOrderNO);
                        db.AbortTransaction();
                        return false;
                    }
                }
                else
                {
                    tradeOrderInfo.FromWarehouseID = info.FromWarehouseID;
                    tradeOrderInfo.ToWarehouseID = info.ToWarehouseID;
                    tradeOrderInfo.TradeOrderID = Guid.NewGuid().ToString();
                    tradeOrderInfo.TradeType = info.TradeType;
                    tradeOrderInfo.SourceOrderNO = info.SourceOrderNO;
                    tradeOrderInfo.ProductNO = info.ProductNO;
                    tradeOrderInfo.Remark = info.Remark;
                    tradeOrderInfo.TradeQty = info.TradeQty;
                    tradeOrderInfo.TradeDate = DateTime.Now.ToString("yyyy-MM-dd");
                    tradeOrderInfo.CreateUser = SysUserInfoBLL.GetCookieUser().UserName;
                    if (!string.IsNullOrWhiteSpace(tradeOrderInfo.TradeOrderNO))
                    {
                        tradeOrderInfo.TradeOrderNO = info.TradeOrderNO;
                    }
                    else
                    {
                        var fromInfo = SysFormNOInfoBLL.GetFormName(tradeOrderInfo);
                        tradeOrderInfo.TradeOrderNO = SysFormNOInfoBLL.GetLastFormNO(fromInfo.TradeOrderNO, out Msg, fromInfo.TradeName, fromInfo.Remark);
                    }
                    sb.Append(BusinessTool.GetInsertSql(tradeOrderInfo));
                }
                if (sb.Length > 0)
                {
                    db.Execute(sb.ToString());
                }
                response.Items.Add(tradeOrderInfo);
            }
            catch (Exception ex)
            {
                response.Message = ex.Message;
                db.AbortTransaction();
                return false;
            }
            db.CompleteTransaction();
            return true;
        }

        /// <summary>
        /// 调拨单登账
        /// </summary>
        /// <param name="info"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public bool AccountWmsTradeOrderInfo_5(WmsTradeOrderInfo info, out string Msg)
        {
            Database db = DB.D();
            db.BeginTransaction();
            StringBuilder sb = new StringBuilder();
            Sql sql = new Sql();
            Msg = string.Empty;
            try
            {
                WmsTradeOrderInfoBLL tradeOrderInfoBLL = new WmsTradeOrderInfoBLL();
                WmsTradeOrderInfo TradeOrderInfo = new WmsTradeOrderInfo();
                WmsTradeSourceBLL tradeSourceBLL = new WmsTradeSourceBLL();
                WmsTradebarcodeBLL tradebarcodeBLL = new WmsTradebarcodeBLL();
                sql = new Sql();
                sql.Append(tradeOrderInfoBLL.MFGSqlSelect);
                sql.Append($"Where WmsTradeOrderInfo.TradeOrderID='{info.TradeOrderID}'");
                var tradeOrderInfo = db.FirstOrDefault<WmsTradeOrderInfo>(sql);
                if (tradeOrderInfo == null)
                {
                    db.AbortTransaction();
                    Msg = ErrorCode.Admin.GetMessage();
                    return false;
                }
                sql = new Sql();
                sql.Append(tradebarcodeBLL.SqlSelect);
                sql.Where($"WmsTradeOrderInfo.TradeOrderID='{info.TradeOrderID}'");
                sql.OrderBy(" BasBarcodeInfo.BarcodeNO");
                var tradebarcodelist = db.Fetch<WmsTradebarcode>(sql);
                if (!tradebarcodelist.Any())
                {
                    db.AbortTransaction();
                    Msg = ErrorCode.Admin.GetMessage();
                    return false;
                }

                #region 库存条码删除

                string IDs = "'',";
                string mIDs = "'',";
                foreach (var item in tradebarcodelist)
                {
                    IDs += "'" + item.BarcodeID + "',";
                    mIDs += "'" + item.BarcodeID + "',";
                    if (item.RState == "0")
                    {
                        db.AbortTransaction();
                        Msg = ErrorCode.Customize.GetMessage(item.BarcodeNO + "条码未确认");
                        return false;
                    }
                }
                IDs = IDs.Substring(0, IDs.Length - 1);
                sql = new Sql();
                sql.Append(@";  Select WmsStockBarcode.*,WmsStockMaterial.StockMaterialID,BasLampInfo.LampNO
                                From WmsStockBarcode
                                Inner JOin BasBarcodeInfo On BasBarcodeInfo.BarcodeID=WmsStockBarcode.BarcodeID
                                Inner JOin WmsStockMaterial On WmsStockBarcode.MaterialID=WmsStockMaterial.MaterialID
                                And WmsStockBarcode.WarehouseID=WmsStockMaterial.WarehouseID
                                And WmsStockBarcode.StorageID=WmsStockMaterial.StorageID
                                And BasBarcodeInfo.BatchNO=WmsStockMaterial.BatchNO
							    Left JOin BasLampInfo On WmsStockBarcode.LampID=BasLampInfo.LampID");
                sql.Where($"BasBarcodeInfo.BarcodeID in ({IDs})");
                sql.OrderBy(" BasBarcodeInfo.BarcodeNO");
                var StockBarcodelist = db.Fetch<WmsStockBarcode>(sql);
                if (!StockBarcodelist.Any())
                {
                    db.AbortTransaction();
                    Msg = string.Format(SysMessageInfoBLL.GetMessageInfo("StockNoExistBarcode"), info.TradeOrderNO);
                    return false;
                }

                IDs = "'',";
                string LampNO = "'',";
                foreach (var item in StockBarcodelist)
                {
                    if (!string.IsNullOrWhiteSpace(item.LampID))
                    {
                        LampNO += "'" + item.LampNO + "',";
                    }
                    sb.Append(@"update WmsStockMaterial set   WmsStockMaterial.StockQty=WmsStockMaterial.StockQty-" + item.StockQty + " where StockMaterialID='" + item.StockMaterialID + "'");
                    IDs += "'" + item.StockBarcodeID + "',";
                }
                IDs = IDs.Substring(0, IDs.Length - 1);
                //删除库存条码表
                sb.Append($"Delete from WmsStockBarcode where  StockBarcodeID  in ({IDs})");
                //删除库存条码表
                sb.Append($"Delete from WmsStockMaterial where  StockQty=0");

                #endregion 库存条码删除

                //灯珠状态改变
                LampNO = LampNO.Substring(0, LampNO.Length - 1);
                sb.Append($"Update BasLampInfo Set RState='0' where BasLampInfo.LampNO  in ({LampNO}) ");
                //基础条码改变
                mIDs = mIDs.Substring(0, mIDs.Length - 1);
                sb.Append($"Update BasBarcodeInfo Set IsStock='0' where BasBarcodeInfo.BarcodeID  in ({mIDs}) ");

                //修改交易主档的状态
                tradeOrderInfo.RState = "2";
                tradeOrderInfo.TradeDate = string.IsNullOrEmpty(info.TradeDate) ? DateTime.Now.ToString("yyyy-MM-dd") : info.TradeDate;

                sb.Append(BusinessTool.GetUpdateSql(tradeOrderInfo));
                if (sb.Length > 0)
                {
                    db.Execute(sb.ToString());
                }
            }
            catch (Exception ex)
            {
                Msg = ex.Message;
                db.AbortTransaction();
                return false;
            }
            db.CompleteTransaction();
            return true;
        }

        /// <summary>
        /// 领料单登账
        /// </summary>
        /// <param name="info"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public bool AccountWmsTradeOrderInfo_3(WmsTradeOrderInfo info, out string Msg)
        {
            Database db = DB.D();
            db.BeginTransaction();
            StringBuilder sb = new StringBuilder();
            Sql sql = new Sql();
            Msg = string.Empty;
            try
            {
                WmsTradeOrderInfo TradeOrderInfo = new WmsTradeOrderInfo();
                WmsTradeOrderInfoBLL tradeOrderInfoBLL = new WmsTradeOrderInfoBLL();
                WmsTradeSourceBLL tradeSourceBLL = new WmsTradeSourceBLL();
                WmsTradebarcodeBLL tradebarcodeBLL = new WmsTradebarcodeBLL();
                sql = new Sql();
                sql.Append(tradeOrderInfoBLL.MFGSqlSelect);
                sql.Append($"Where WmsTradeOrderInfo.TradeOrderID='{info.TradeOrderID}'");
                var tradeOrderInfo = this.GetSingeModel(sql);
                if (tradeOrderInfo == null)
                {
                    db.AbortTransaction();
                    Msg = ErrorCode.Admin.GetMessage();
                    return false;
                }
                sql = new Sql();
                sql.Append(tradebarcodeBLL.SqlSelect);
                sql.Where($"WmsTradeOrderInfo.TradeOrderID='{info.TradeOrderID}'");
                sql.OrderBy(" BasBarcodeInfo.BarcodeNO");
                var tradebarcodelist = db.Fetch<WmsTradebarcode>(sql);
                if (!tradebarcodelist.Any())
                {
                    db.AbortTransaction();
                    Msg = ErrorCode.Admin.GetMessage();
                    return false;
                }

                #region 库存条码删除

                string IDs = "'',";
                string mIDs = "'',";
                foreach (var item in tradebarcodelist)
                {
                    IDs += "'" + item.BarcodeID + "',";
                    mIDs += "'" + item.BarcodeID + "',";
                    //if (item.RState == "0")
                    //{
                    //    db.AbortTransaction();
                    //    Msg = ErrorCode.Customize.GetMessage(item.BarcodeNO + "条码未确认");
                    //    return false;
                    //}
                }
                IDs = IDs.Substring(0, IDs.Length - 1);
                sql = new Sql();
                sql.Append(@";  Select WmsStockBarcode.*,WmsStockMaterial.StockMaterialID,BasLampInfo.LampNO
                                From WmsStockBarcode
                                Inner JOin BasBarcodeInfo On BasBarcodeInfo.BarcodeID=WmsStockBarcode.BarcodeID
                                Inner JOin WmsStockMaterial On WmsStockBarcode.MaterialID=WmsStockMaterial.MaterialID
                                And WmsStockBarcode.WarehouseID=WmsStockMaterial.WarehouseID
                                And WmsStockBarcode.StorageID=WmsStockMaterial.StorageID
                                And BasBarcodeInfo.BatchNO=WmsStockMaterial.BatchNO
							    Left JOin BasLampInfo On WmsStockBarcode.LampID=BasLampInfo.LampID");
                sql.Where($"BasBarcodeInfo.BarcodeID in ({IDs})");
                sql.OrderBy(" BasBarcodeInfo.BarcodeNO");
                var StockBarcodelist = db.Fetch<WmsStockBarcode>(sql);
                if (!StockBarcodelist.Any())
                {
                    db.AbortTransaction();
                    Msg = string.Format(SysMessageInfoBLL.GetMessageInfo("StockNoExistBarcode"), info.TradeOrderNO);
                    return false;
                }

                IDs = "'',";
                string LampNO = "'',";
                foreach (var item in StockBarcodelist)
                {
                    if (!string.IsNullOrWhiteSpace(item.LampID))
                    {
                        LampNO += "'" + item.LampNO + "',";
                    }
                    sb.Append(@"update WmsStockMaterial set   WmsStockMaterial.StockQty=WmsStockMaterial.StockQty-" + item.StockQty + " where StockMaterialID='" + item.StockMaterialID + "'");
                    IDs += "'" + item.StockBarcodeID + "',";
                }
                IDs = IDs.Substring(0, IDs.Length - 1);
                //删除库存条码表
                sb.Append($"Delete from WmsStockBarcode where  StockBarcodeID  in ({IDs})");
                //删除库存条码表
                sb.Append($"Delete from WmsStockMaterial where  StockQty=0");

                #endregion 库存条码删除

                //灯珠状态改变
                LampNO = LampNO.Substring(0, LampNO.Length - 1);
                sb.Append($"Update BasLampInfo Set RState='0' where BasLampInfo.LampNO  in ({LampNO}) ");
                //基础条码改变
                mIDs = mIDs.Substring(0, mIDs.Length - 1);
                sb.Append($"Update BasBarcodeInfo Set IsStock='0' where BasBarcodeInfo.BarcodeID  in ({mIDs}) ");

                //修改交易主档的状态
                tradeOrderInfo.RState = "2";
                tradeOrderInfo.TradeDate = string.IsNullOrEmpty(info.TradeDate) ? DateTime.Now.ToString("yyyy-MM-dd") : info.TradeDate;

                sb.Append(BusinessTool.GetUpdateSql(tradeOrderInfo));
                if (sb.Length > 0)
                {
                    db.Execute(sb.ToString());
                }
            }
            catch (Exception ex)
            {
                Msg = ex.Message;
                db.AbortTransaction();
                return false;
            }
            db.CompleteTransaction();
            return true;
        }

        /// <summary>
        /// 获取仓库交易对象(含交易主档，需求明细和条码明细)
        /// </summary>
        /// <param name="TradeOrderNO">交易单号和交易ID</param>
        /// <param name="Msg"></param>
        /// <returns></returns>
        public static WmsTradeOrderInfo GerTradeOrderInfo(string TradeOrderNO, out string Msg)
        {
            WmsTradeOrderInfo Info = new WmsTradeOrderInfo();
            Msg = string.Empty;
            Database db = DB.D();
            Sql sql = new Sql();

            try
            {
                sql = new Sql();
                sql.Append(@";Select
                            WmsTradeOrderInfo.*,
                            BasWarehouseInfo.WarehouseNO FromWarehouseNO
                            From WmsTradeOrderInfo
                            Left JOIn BasWarehouseInfo ON WmsTradeOrderInfo.FromWarehouseID=BasWarehouseInfo.WarehouseID");
                sql.Where("(WmsTradeOrderInfo.TradeOrderID='" + TradeOrderNO + "' or WmsTradeOrderInfo.TradeOrderNO='" + TradeOrderNO + "')");
                Info = db.FirstOrDefault<WmsTradeOrderInfo>(sql);
                if (Info == null)
                {
                    Msg = "No Order";
                    return null;
                }
                if (Info.RState != "2")
                {
                    Msg = " Document status is incorrect, unable to post back to ERP ";
                    return null;
                }
                sql = new Sql();
                sql.Append(@"Select
                            WmsTradeSource.*,
                            WmsTradeOrderInfo.TradeOrderNO,
                            BasMaterialInfo.MaterialNO,
                            BasStorageInfo.StorageNO,
                            BasWarehouseInfo.WarehouseNO
                            From  WmsTradeSource
                            Inner JOin WmsTradeOrderInfo ON WmsTradeOrderInfo.TradeOrderID=WmsTradeSource.TradeOrderID
                            Inner Join BasMaterialInfo ON WmsTradeSource.MaterialID=BasMaterialInfo.MaterialID
                            Inner JOIn BasStorageInfo ON WmsTradeSource.StorageID=BasStorageInfo.StorageID
                            Inner JOIn BasWarehouseInfo ON BasStorageInfo.WarehouseID=BasWarehouseInfo.WarehouseID
                            ");
                sql.Where("(WmsTradeOrderInfo.TradeOrderID='" + TradeOrderNO + "' or WmsTradeOrderInfo.TradeOrderNO='" + TradeOrderNO + "')");
                sql.OrderBy("WmsTradeOrderInfo.TradeOrderNO,CONVERT(INT, isnull( WmsTradeSource.ErpItem,0))");
                List<WmsTradeSource> Sources = db.Fetch<WmsTradeSource>(sql);
                if (Sources != null)
                {
                    Info.TradeSources = Sources;
                }
                sql = new Sql();
                sql.Append(@";Select
                            WmsTradebarcode.*,
                            BasBarcodeInfo.BarcodeNO,
                            BasBarcodeInfo.BatchNO,
                            CONVERT(INT, isnull( WmsTradeSource.ErpItem,0)) ErpItem,
                            WmsTradeOrderInfo.TradeOrderNO,
                            BasMaterialInfo.MaterialNO,
                            BasStorageInfo.StorageNO FromStorageNO,
                            BasWarehouseInfo.WarehouseNO FromWarehouseNO,
                            BasLampInfo.LampNO FromLampNO,
                            ToStorageInfo.StorageNO ToStorageNO,
                            ToWarehouseInfo.WarehouseNO ToWarehouseNO,
                            ToLampInfo.LampNO ToLampNO
                            From WmsTradebarcode
                            Inner JOin WmsTradeOrderInfo ON WmsTradeOrderInfo.TradeOrderID=WmsTradebarcode.TradeOrderID
                            Inner JOIn BasBarcodeInfo ON WmsTradebarcode.BarcodeID=BasBarcodeInfo.BarcodeID
                            Inner JOin WmsTradeSource ON WmsTradebarcode.TradeSourceID=WmsTradeSource.TradeSourceID
                            Inner Join BasMaterialInfo ON BasBarcodeInfo.MaterialID=BasMaterialInfo.MaterialID
                            Inner JOIn BasStorageInfo ON WmsTradebarcode.StorageID=BasStorageInfo.StorageID
                            Inner JOIn BasWarehouseInfo ON BasStorageInfo.WarehouseID=BasWarehouseInfo.WarehouseID
                            Left JOIn  BasLampInfo ON WmsTradebarcode.LampID=BasLampInfo.LampID
                            Left JOIn BasStorageInfo ToStorageInfo ON WmsTradebarcode.ToStorageID=ToStorageInfo.StorageID
                            Left JOIn BasWarehouseInfo ToWarehouseInfo ON ToStorageInfo.WarehouseID=ToWarehouseInfo.WarehouseID
                            Left JOIn  BasLampInfo ToLampInfo ON WmsTradebarcode.ToLampID=ToLampInfo.LampID");
                sql.Where("(WmsTradeOrderInfo.TradeOrderID='" + TradeOrderNO + "' or WmsTradeOrderInfo.TradeOrderNO='" + TradeOrderNO + "')");
                sql.OrderBy("WmsTradeOrderInfo.TradeOrderNO,CONVERT(INT, isnull( WmsTradeSource.ErpItem,0)),BasBarcodeInfo.BarcodeNO");
                List<WmsTradebarcode> Barcodes = db.Fetch<WmsTradebarcode>(sql);
                if (Barcodes != null)
                {
                    Info.Tradebarcodes = Barcodes;
                }

                return Info;
            }
            catch (Exception ex)
            {
                Msg = ex.Message;
                return null;
            }
        }

        /// <summary>
        /// 单据审核
        /// </summary>
        /// <param name="Infos">单据列表'</param>
        /// <param name="CheckUser">审核人员</param>
        /// <param name="Msg">反馈信息</param>
        /// <returns></returns>
        public static bool CheckTradeOrder(List<WmsTradeOrderInfo> Infos, string CheckUser, out string Msg)
        { 
            Msg=string.Empty;
            try
            {
                Database db = DB.D();
                 StringBuilder sb = new StringBuilder();
                foreach (WmsTradeOrderInfo info in Infos)
                {
                    sb.Append(@"Update WmsTradeOrderInfo Set RState='1',UpdateTime='"+BusinessTool.GetDateTime()+"',UpdateUser='"+CheckUser+ "' Where TradeOrderID='" + info.TradeOrderID+ "' Or  TradeOrderNO='" + info.TradeOrderNO + "'");
                }
                if (sb.Length > 0)
                {
                    db.Execute(sb.ToString());
                }
               
                return true;
            }
            catch (Exception ex)
            {
                Msg = ex.ToString();
                return false;
            }
        }
    }
}